package com.mmy.dao.sys;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.sys.VSysOrg;

public interface VSysOrgRepository extends JpaRepository<VSysOrg, Integer> {
	/* 按ID查找 */
	@Query(value = "select * from vSysOrg where iOrgID=?1", nativeQuery = true)
	VSysOrg findByID(int iOrgID);

	/*
	 * 按搜索条件搜索
	 */
	@Query(value = "select *  from  vSysOrg where  (cOrgName like %:cOrgName%)  and (:iUpOrgID = \"\" or iUpOrgID = :iUpOrgID)and (:iZoneID = \"\" or iZoneID = :iZoneID)", nativeQuery = true)
	List<VSysOrg> findSearch(@Param("cOrgName") String strOrgName, @Param("iUpOrgID") String strOrgID,
			@Param("iZoneID") String strZoneID);
	
	@Query(value = "select * from  vSysOrg where cSpare5 = 0", nativeQuery = true)
	List<VSysOrg> findCheck();
	
	@Query(value = "select count(1) from vSysOrg where cSpare5 = 0", nativeQuery = true)
	int queryCheckCount();
	
	/**
	 * 后台旅行社首页显示 超级管理员登录
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select * from vSysOrg where (:strOrgName = \"\" or cOrgName like %:strOrgName%) "
			+ "and (:strUpOrgID = \"\" or iUpOrgID like %:strUpOrgID%) "
			+ "and (:strZoneID = \"\" or iZoneID like %:strZoneID%) limit :startIndex,:count", nativeQuery = true)
	List<VSysOrg> findSearchALL(@Param("strOrgName") String strOrgName, @Param("strUpOrgID") String strUpOrgID,
			@Param("strZoneID") String strZoneID, @Param("startIndex") int startIndex, @Param("count") int count);

	/**
	 * 后台旅行社首页显示 非超级管理员登录
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select * from vSysOrg where (:strOrgName = \"\" or cOrgName like %:strOrgName%) "
			+ "	and (:strZoneID = \"\" or iZoneID like %:strZoneID%) "
			+ "and (iOrgID=:iOrgID or iUpOrgID=:iOrgID) limit :startIndex,:count", nativeQuery = true)
	List<VSysOrg> findSearchByOrgID(@Param("iOrgID") int iOrgID, @Param("strOrgName") String strOrgName,
			@Param("strZoneID") String strZoneID, @Param("startIndex") int startIndex, @Param("count") int count);

	/**
	 * 后台旅行社首页显示 超级管理员登录 分页
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.02.22
	 */
	@Query(value = "select count(*)  from vSysOrg where (:strOrgName = \"\" or cOrgName like %:strOrgName%) "
			+ "and (:strUpOrgID = \"\" or iUpOrgID like %:strUpOrgID%) "
			+ "and (:strZoneID = \"\" or iZoneID like %:strZoneID%)", nativeQuery = true)
	int countALL(@Param("strOrgName") String strOrgName, @Param("strUpOrgID") String strUpOrgID,
			@Param("strZoneID") String strZoneID);

	/**
	 * 后台航班管理中显示自己公司的航班信息 获取分页
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.02.22
	 */
	@Query(value = "select count(*)  from vSysOrg where  (:strOrgName = \"\" or cOrgName like %:strOrgName%) "
			+ "and (:strZoneID = \"\" or iZoneID like %:strZoneID%) "
			+ "and (iOrgID=:iOrgID or iUpOrgID=:iOrgID) ", nativeQuery = true)
	int countByOrgID(@Param("iOrgID") int iOrgID, @Param("strOrgName") String strOrgName,
			@Param("strZoneID") String strZoneID);

}
